﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Drawing;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace cls.models
{
    public partial class FileUpLoad : System.Web.UI.Page
    {
        static String AppName = "";
        static String OrgType = "";
        static String OrgCode = "";
        static String OrgName = "";
        static String ReturnUrl = "";
        static String UserName = "";
        static String ExcelFilePath = "";
        static DateTime DateTimeNow;

        protected void Page_Load(object sender, EventArgs e)
        {
            
//DateTimeNow = DateTime.Now;
            UserName = User.Identity.Name;
            AppName = Request.QueryString["App"];
            OrgType = Request.QueryString["OrgType"];
            //OrgCode = Request.QueryString["OrgCode"];
            OrgCode = getOrgCodeByUserName(UserName);
            OrgName = getOrgNameByUserName(UserName);

            if (AppName == null || AppName.Equals(""))
            {
                Response.Redirect("../Default.aspx");
            }

            Label_AppName.Text = "";

            if (AppName.Equals("Empstat"))
            {
                Label_AppName.Text = "您要上传的是门店人员统计电子表单，请确保要上传的文件没有被其他程序打开！";
                ReturnUrl = "EmpstatMgr.aspx";
            }
            else if (AppName.Equals("Backorder"))
            {
                Label_AppName.Text = "您要上传的是门店缺货电子表单，请确保要上传的文件没有被其他程序打开！";
                ReturnUrl = "BackorderMgr.aspx";
            }
            else if (AppName.Equals("Issuseplu"))
            {
                Label_AppName.Text = "您要上传的是门店异常商品电子表单，请确保要上传的文件没有被其他程序打开！<br />";
                Label_AppName.Text += "重要说明：Excel表中的[退货数量]和[现有库存]列不可为空，必须填写数字！";
                ReturnUrl = "IssusepluMgr.aspx";
            }
            else if (AppName.Equals("Saleplan"))
            {
                Label_AppName.Text = "您要上传的是月度销售计划分解电子表单，请确保要上传的文件没有被其他程序打开！";
                ReturnUrl = "SaleplanMgr.aspx";
            }

            //显示用户提示：
            Label_AppName.ForeColor = Color.Red;
            //Label_AppName.Text = "程序出了一点小问题......先不要上传！";

            if (UserName == null || UserName.Equals(""))
            {
                Response.Redirect("../Default.aspx");
            }
            else if (UserName.Trim().Equals("徐津煜"))
            {
                Button_Test.Visible = true;
            }
            else
            {
                Button_Test.Visible = false;
            }

            if (!Page.IsPostBack)
            {
                Button_FileUpload.Enabled = true;
            }
        }

        protected void Button_FileUpload_Click(object sender, EventArgs e)
        {
            //Label_DebugMsg.Text = "";
            //Label_DebugMsg.Text = "======== 以下是调试信息，用于跟踪程序错误，不是“乱码”。 ========<br />";

            Label_UploadStatus.Text = "";

            //点击按钮的时间
            DateTimeNow = DateTime.Now;

            if (FileUpload_ExcelFile.HasFile)
            {
                Label_DebugMsg.Text = "";

                //允许的文件类型


                SaveExcelFileToServer(GetExcelFilePathName(), UserName);

                //加入AppName判定
                ExcelImportToSQLDB(GetExcelFilePathName(), AppName, DateTimeNow);

                //状态显示
                Label_UploadStatus.ForeColor = Color.Red;
                //Label_UploadStatus.Text = "这里显示的是简体中文的汉字，不是“乱码”！<br />";
                //应该是成功了......
                Label_UploadStatus.Text = "上传成功！<br />";
            }
            else
            {
                Label_UploadStatus.Text = "请选择正确的文件。";
            }
        }

        //AppName参数用于判定上传程序
        protected void ExcelImportToSQLDB(String ExcelFIlePathName, String AppName, DateTime ImportDatatime)
        {
            //首先记录上传时间
            //RecLastInsDatetime(AppName, OrgCode, ExcelFilePathName, UserName, DateTimeNow);

            //用存储过程实现太麻烦了，还是直接用语句吧......
            //http://blog.csdn.net/andylaufzf/article/details/2143716
            //http://www.cnblogs.com/lhking/archive/2009/06/08/1499002.html

            String SheetName = "";
            String InsertTableName = "";
            String InsertSqlString = "";

            ArrayList TableColumnNames = new ArrayList();

            String ColumnNames = "";

            //注意
            //AppName判定不同的Sql Table、SheetName
            //CA1062：验证公共方法的参数
            //http://msdn.microsoft.com/query/dev12.query?appId=Dev12IDEF1&l=ZH-CN&k=k(CA1062);k(TargetFrameworkMoniker-.NETFramework,Version%3Dv4.0)&rd=true
            if (AppName.Equals("Backorder"))
            {
                TableColumnNames = GetDbTableColumnName("u_cls_Backorder_Table");
                SheetName = "Backorder$";
                InsertTableName = "[dbo].[u_cls_Backorder_Table]";
            }
            else if (AppName.Equals("Empstat"))
            {
                TableColumnNames = GetDbTableColumnName("u_cls_Empstat_Table");
                SheetName = "Empstat$";
                InsertTableName = "[dbo].[u_cls_Empstat_Table]";
            }
            else if (AppName.Equals("Issuseplu"))
            {
                TableColumnNames = GetDbTableColumnName("u_cls_Issuseplu_Table");
                SheetName = "Issuseplu$";
                InsertTableName = "[dbo].[u_cls_Issuseplu_Table]";
            }
            else if (AppName.Equals("Saleplan"))
            {
                TableColumnNames = GetDbTableColumnName("u_cls_Saleplan_Table");
                SheetName = "Saleplan$";
                InsertTableName = "[dbo].[u_cls_Saleplan_Table]";
            }

            //注意
            //AppName判定不同的DataSet
            DataSet ExcelDataSet = GetExcelDataSet(ExcelFIlePathName, SheetName, AppName);

            for (int i = 0; i < TableColumnNames.Count; i++)
            {
                ColumnNames += "[" + TableColumnNames[i].ToString() + "]" + ",";
            }

            //定义最后一个,的下标。
            //注意,是Char不是String，所以用','而不是","
            int LastCharIndex = ColumnNames.LastIndexOf(',');

            //移除最后一个","
            ColumnNames = ColumnNames.Remove(LastCharIndex);

            //调试信息
            //显示所有列名，加入分隔符，用于INSERT INTO语句            
            //Label_DebugMsg.Text += LastCharIndex + "<br />";
            //Label_DebugMsg.Text += ColumnNames + "<br />";            

            SqlConnection SqlConn = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConn.Open();

            SqlCommand InsertCommand = new SqlCommand();
            InsertCommand.Connection = SqlConn;
            InsertCommand.CommandType = CommandType.Text;

            InsertCommand.Parameters.Add(new SqlParameter("@ImportDatatime", SqlDbType.DateTime));
            InsertCommand.Parameters["@ImportDatatime"].Value = ImportDatatime;

            //构建SQL INSERT语句中的VALUES
            for (int h = 0; h < ExcelDataSet.Tables[0].Rows.Count; h++)
            {
                //GUID作为Id列，
                String ColumeValues = "'" + System.Guid.NewGuid().ToString() + "'" + ",";

                //Saleplan的特殊处理
                if (AppName.Equals("Saleplan"))
                {
                    //ColumeValues += "'" + OrgType + "','" + OrgCode + "',";
                    ColumeValues += "'" + OrgType + "',";
                }
                //Backorder上传时自动加入OrgCode和OrgCode
                else if (AppName.Equals("Backorder"))
                {
                    //门店上传时，自动取得OrgCode和OrgName
                    ColumeValues += "'" + OrgCode + "'" + "," + "'" + OrgName + "'" + ",";
                }

                //根据列名得到值
                for (int k = 0; k < ExcelDataSet.Tables[0].Columns.Count; k++)
                {
                    //存在问题
                    //从数据类型 varchar 转换为 numeric 时出错。
                    //u_cls_Issuseplu_Table表的InvCount(decimal(18,2))和InvCount(decimal(18,2))
                    //除了改成nvarchar，还有什么方法......
                    ColumeValues += "'" + ExcelDataSet.Tables[0].Rows[h][k].ToString() + "'" + ",";
                }

                //移除VALUES中的最后一个","
                int n = ColumeValues.LastIndexOf(',');
                ColumeValues = ColumeValues.Remove(n);

                if (AppName.Equals("Empstat"))
                {
                    //ColumeValues的值是不同的SqlDbType，无法使用参数化查询。
                    //这是个问题。
                    //InsertSqlString = "INSERT INTO [dbo].[u_cls_Empstat_Table] (" + ColumnNames + ") VALUES (" + ColumeValues + ",'" + DateTimeNow + "','" + UserName + "','','','')";

                    //2014-11-18日修改，更新模版
                    InsertSqlString = "INSERT INTO [dbo].[u_cls_Empstat_Table] ([id],[OrgCode],[OrgName],[StdCount],[EmpNames],[EmpSex],[EmpTitle],[EmpJoinDate],[EmpAddr],[EmpMobile],[ImportDatetime],[ImportUserName],[LastModifyDatetime],[LastModifyUserName],[StatDatetime]) VALUES (" + ColumeValues + ",'" + DateTimeNow + "','" + UserName + "','','','')";
                }
                else if (AppName.Equals("Backorder"))
                {
                    //2015-01-12，更新上传模版。
                    //InsertSqlString = "INSERT INTO [dbo].[u_cls_Backorder_Table] (" + ColumnNames + ") VALUES (" + ColumeValues + ",'" + DateTimeNow + "')";
                    
                    //不使用ColumnNames，我觉得不方便......
                    //DateTime在参数化查询时精确到毫秒
                    //DateTime在下面的语句中精确到秒，毫秒是000
                    //InsertSqlString = "INSERT INTO " + InsertTableName + " ([Id],[OrgCode],[OrgName],[ClsCode],[ClsName],[PluCode],[PluName],[LW_SaleCount],[SkillCount],[Comment],[YH_BillNo],[YH_Count],[ImportDatatime]) VALUES (" + ColumeValues + ",'" + DateTimeNow + "')";

                    InsertSqlString = "INSERT INTO " + InsertTableName + " ([Id],[OrgCode],[OrgName],[ClsCode],[ClsName],[PluCode],[PluName],[LW_SaleCount],[SkillCount],[Comment],[YH_BillNo],[YH_Count],[ImportDatatime]) VALUES (" + ColumeValues + ",@ImportDatatime)";
                    
                    //InsertCommand加入参数
                    //这种自虐的写法导致Value=null
                    //InsertCommand.Parameters.Add(new SqlParameter("@ImportDatatime", SqlDbType.DateTime).Value = ImportDatatime);
                    //定义和赋值分开写
                    //InsertCommand.Parameters.Add(new SqlParameter("@ImportDatatime", SqlDbType.DateTime));
                    //InsertCommand.Parameters["@ImportDatatime"].Value = ImportDatatime;
                }
                else if (AppName.Equals("Issuseplu"))
                {
                    InsertSqlString = "INSERT INTO [dbo].[u_cls_Issuseplu_Table] (" + ColumnNames + ") VALUES (" + ColumeValues + ",'" + DateTimeNow + "','" + UserName + "')";
                }
                else if (AppName.Equals("Saleplan"))
                {                    
                    InsertSqlString = "INSERT INTO [dbo].[u_cls_Saleplan_Table] ([id],[OrgType],[PlanYearMonth],[OrgCode],[OrgName],[OrgPlan],[WeekOne_ShopPlan],[WeekTwo_ShopPlan],[WeekThree_ShopPlan],[WeekFour_ShopPlan],[WeekDateRemark],[ImportDatetime],[ImportUserName],[InsDatetime],[InsUserName]) VALUES (" + ColumeValues + ",'" + DateTimeNow + "','" + UserName + "','','')";

                    //InsertSqlString = "INSERT INTO [dbo].[u_cls_Saleplan_Table] ([id],[OrgType],[PlanYearMonth],[OrgCode],[OrgName],[OrgPlan],[WeekOne_ShopPlan],[WeekTwo_ShopPlan],[WeekThree_ShopPlan],[WeekFour_ShopPlan],[WeekDateRemark],[InsDatetime],[InsUserName],[ImportDatetime],[ImportUserName]) VALUES (" + ColumeValues + ",'" + DateTimeNow + "','" + UserName + "','','')";
                }

                ///调试信息
                //Label_DebugMsg.Text += "ColumeValues[" + ColumeValues + "]:<br />" ;

                //调试信息，显示INSERT INTO语句                
                //Label_DebugMsg.Text += "InsertSqlString[" + h + "]:<br />" + InsertSqlString + "<br />";

                InsertCommand.CommandText = InsertSqlString;

                try
                {
                    InsertCommand.ExecuteNonQuery();

                    Label_UploadStatus.Text = "上传成功！<br />";
                }
                catch (SqlException e)
                {
                    Label_UploadStatus.Text += "上传失败，InsertCommand.ExecuteNonQuery()错误！行：291<br />";
                    Label_UploadStatus.Text += "错误信息：" + e.Message + "<br />";
                    Label_UploadStatus.Text += "跟踪信息：" + e.StackTrace + "<br />";
                    //Label_DebugMsg.Text += e.Message + "<br />";
                    //Label_DebugMsg.Text += "------------------------------------------------------------------";
                    //Label_DebugMsg.Text += e.StackTrace + "<br />";
                    //Label_DebugMsg.Text += "------------------------------------------------------------------";
                    WriteLog("e.Message:<br />" + e.Message + "<br />" + "e.StackTrace:<br />" + e.StackTrace + "<br />");
                }
                //finally
                //{

                //}
            }

            InsertCommand.Dispose();
            SqlConn.Close();
        }

        protected String GetExcelFilePathName()
        {
            String ExcelFilePathName = "";

            ExcelFilePath = MapPath("~/u_ExcelFiles/");
            if (FileUpload_ExcelFile.HasFile)
            {
                String ExcelFileName = Path.GetFileNameWithoutExtension(FileUpload_ExcelFile.FileName) + "_" + DateTimeNow.ToString("yyyy-MM-dd_HHmmss", new CultureInfo("zh-CN")) + "_" + UserName + Path.GetExtension(FileUpload_ExcelFile.FileName);

                ExcelFilePathName = ExcelFilePath + ExcelFileName;
            }

            return ExcelFilePathName;
        }
        protected void SaveExcelFileToServer(String ExcelFilePathName, String UserName)
        {
            FileUpload_ExcelFile.PostedFile.SaveAs(ExcelFilePathName);
            
            RecLastInsDatetime(AppName, OrgCode, ExcelFilePathName, UserName, DateTimeNow);
        }

        protected void RecLastInsDatetime(String AppName, String OrgCode, String FilePathName, String UserName, DateTime InsDatetime)
        {
            //这个也可以用触发器实现，未明确优劣，先这样吧......
            SqlConnection SqlConn = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConn.Open();

            SqlCommand InsertCommand = new SqlCommand("INSERT INTO [dbo].[u_cls_LastInsDatetime] ([ApplicationName],[OrgCode],[FilePathName],[UploadUserName],[LastInsDatetime]) VALUES(@ApplicationName,@OrgCode,@FilePathName,@UploadUserName,@LastInsDatetime)", SqlConn);
            InsertCommand.CommandType = CommandType.Text;

            SqlCommand SelectCommand = new SqlCommand("SELECT TOP(1) LastInsDatetime FROM [dbo].[u_cls_LastInsDatetime] WHERE ApplicationName = '" + AppName + "' AND OrgCode='" + OrgCode + "' ORDER BY LastInsDatetime DESC", SqlConn);
            SelectCommand.CommandType = CommandType.Text;

            SqlCommand DeleteCommand = new SqlCommand("DELETE FROM [dbo].[u_cls_LastInsDatetime] WHERE ApplicationName = '" + AppName + "' AND OrgCode='" + OrgCode + "'", SqlConn);
            DeleteCommand.CommandType = CommandType.Text;

            SqlDataReader SqlDr = SelectCommand.ExecuteReader();

            if (SqlDr.HasRows)
            {
                //执行DeleteCommand之前，必须首先关闭SqlDataReader，以及SelectCommand
                SqlDr.Close();

                //执行DeleteCommand之前，必须首先关闭SqlDataReader，以及SelectCommand
                SelectCommand.Dispose();

                DeleteCommand.ExecuteNonQuery();
            }

            SqlDr.Close();
            DeleteCommand.Dispose();

            //参考简洁的语法
            //someCommand.Parameters.Add("@username", SqlDbType.NChar).Value = name;
            //http://msdn.microsoft.com/query/dev12.query?appId=Dev12IDEF1&l=ZH-CN&k=k(CA2100);k(TargetFrameworkMoniker-.NETFramework,Version%3Dv4.0)&rd=true
            SqlParameter[] InsertParameter = new SqlParameter[5];
            InsertParameter[0] = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 50);
            InsertParameter[1] = new SqlParameter("@OrgCode", SqlDbType.NVarChar, 4);
            InsertParameter[2] = new SqlParameter("@FilePathName", SqlDbType.NVarChar, 500);
            InsertParameter[3] = new SqlParameter("@UploadUserName", SqlDbType.NVarChar, 4);
            InsertParameter[4] = new SqlParameter("@LastInsDatetime", SqlDbType.DateTime);

            InsertParameter[0].Value = AppName;
            InsertParameter[1].Value = OrgCode;
            InsertParameter[2].Value = FilePathName;
            InsertParameter[3].Value = UserName;
            InsertParameter[4].Value = InsDatetime;

            for (int i = 0; i < InsertParameter.Length; i++)
            {
                InsertCommand.Parameters.Add(InsertParameter[i]);
            }
            //InsertCommand.Parameters.Add(InsertParameter[1]);
            //InsertCommand.Parameters.Add(InsertParameter[2]);            

            try
            {
                InsertCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                WriteLog(e.Message + "||" + e.StackTrace);
            }
            InsertCommand.Dispose();

            SqlConn.Close();
        }

        //注意
        //AppName判定
        public DataSet GetExcelDataSet(String ExcelFilePathName, String SheetName, String AppName)
        {
            DataSet ExcelDataSet = new DataSet();

            //备忘：
            //2007 Office system 驱动程序：数据连接组件（32位）
            //http://www.microsoft.com/zh-cn/download/details.aspx?id=23734

            //这里使用：
            //Microsoft Access 2010 数据库引擎可再发行程序包（64位）
            //http://www.microsoft.com/zh-CN/download/details.aspx?id=13255
            //如果要连接到 Microsoft Office Excel 数据，请根据 Excel 文件类型添加相应的 OLEDB 连接字符串扩展属性：
            //
            //文件类型（扩展名）                                 扩展属性
            //---------------------------------------------------------------------------------------------
            //Excel 97-2003 工作簿（.xls）                      “Excel 8.0”
            //Excel 2007-2010 工作簿（.xlsx）                   “Excel 12.0 Xml”
            //启用宏的 Excel 2007-2010 工作簿（.xlsm）           “Excel 12.0 宏”
            //Excel 2007-2010 非 XML 二进制工作簿（.xlsb）      “Excel 12.0”

            //关于HDR和IMEX：
            //http://blog.sina.com.cn/s/blog_6e001be701016yi8.html
            //HDR 表示第一行是否是标题行
            //若为 YES，则第一行是标题行（即列名称），不是数据；
            //若为 NO，则第一行不是标题行，跟后面的行一样，是数据。
            //IMEX 表示混合数据类型时如何处理。
            //若为 0，则为输出模式，此情况下只能用作写入 Excel；
            //若为 1，则为输入模式，此情况下只能用作读取 Excel，并且始终将 Excel 数据作为文本类型读取；
            //若为 2，则为连接模式，此情况下既可用作写入、也可用作读取。

            //ConnectionString格式：
            //http://www.connectionstrings.com/ace-oledb-12-0/

            String ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePathName.Replace("\\", "\\\\") + ";Extended Properties=\"Excel 12.0 Xml;HDR=Yes\"";

            OleDbConnection OleDBConn = new OleDbConnection(ConString);

            //[OleDbException (0x80004005): Microsoft Access 数据库引擎无法打开文件“”或向其写入数据。它已经被其他用户以独占方式打开，或者您没有查看和写入其数据的权限。]
            //解决方法：上传至非系统盘目录，然后读取。
            OleDBConn.Open();

            OleDbDataAdapter OleDbDA = new OleDbDataAdapter("SELECT * FROM [" + SheetName + "]", OleDBConn);

            //OleDbDA.Fill(ExcelDataSet, "XLData");
            OleDbDA.Fill(ExcelDataSet);

            OleDbDA.Dispose();
            OleDBConn.Close();

            return ExcelDataSet;
        }

        public ArrayList ExcelSheetNames(String FilePath)
        {
            ArrayList ExcelSheets = new ArrayList();

            String ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\cls.haoshoucheng.com\\site\\u_ExcelFiles\\测试_缺货管控表（模版）.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=Yes\"";

            OleDbConnection conn = new OleDbConnection(ConString);
            conn.Open();

            DataTable SheetNames = conn.GetOleDbSchemaTable
            (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

            conn.Close();

            foreach (DataRow dr in SheetNames.Rows)
            {
                ExcelSheets.Add(dr[2]);
            }

            return ExcelSheets;
        }

        protected void Button_Test_Click(object sender, EventArgs e)
        {
            Label_DebugMsg.Text = "";

            //GridView_ExcelGridView.DataSource = GetExcelDataSet(null, null);
            //GridView_ExcelGridView.DataBind();

            //String[] SheetNames = ExcelSheetNames(null).ToArray();
            //ArrayList SheetNames = ExcelSheetNames(null);

            //for (int i = 0; i < SheetNames.Count; i++)
            //{
            //Label_DebugMsg.Text += "SheetNames.Count:" + SheetNames.Count + "<br />";
            //Label_DebugMsg.Text += "Count:" + i + " - " + SheetNames[i].ToString() + "<br />";
            //Label_DebugMsg.Text +=  "<br />";
            //}

            //ExcelImportToSQLDB(GetExcelFilePathName(), AppName);
        }

        //注意
        //AppName判定
        //获取数据库中表的所有列，用于构建SQL语句。
        public ArrayList GetDbTableColumnName(string TableName)
        {
            ArrayList AL_ColumnNames = new ArrayList();

            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            SqlCommand SelectCommand = new SqlCommand("SELECT b.name FROM sysobjects a INNER JOIN syscolumns b ON a.id = b.id WHERE (a.name = '" + TableName + "')", SqlConnection);

            SqlDataReader SqlDr = SelectCommand.ExecuteReader();
            while (SqlDr.Read())
            {
                AL_ColumnNames.Add(SqlDr.GetString(0));
            }

            SqlDr.Close();
            SelectCommand.Dispose();
            SqlConnection.Close();

            return AL_ColumnNames;
        }

        protected void Button_Return_Click(object sender, EventArgs e)
        {
            Response.Redirect(ReturnUrl);
        }

        protected void WriteLog(String LogMessage)
        {
            System.Guid id = Guid.NewGuid();
            DateTime LogDatetime = DateTime.Now;

            SqlConnection SqlConn = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConn.Open();

            String InsertCommandStr = "INSERT INTO [dbo].[u_cls_Log] ([id],[LogDatetime],[ApplicationName],[ApplicationUserName],[LogSource],[LogMessage]) VALUES ('" + id + "','" + LogDatetime + "','" + AppName + "','" + UserName + "','FileUpload.aspx','" + LogMessage + "')";

            SqlCommand InsertCommand = new SqlCommand(InsertCommandStr, SqlConn);

            //InsertCommand.Parameters.Add();

            //Label_DebugMsg.Text += "======== 写入日志 ========<br />";
            //Label_DebugMsg.Text += InsertCommandStr;

            InsertCommand.CommandType = CommandType.Text;
            //InsertCommand.ExecuteNonQuery();
            InsertCommand.Dispose();
            SqlConn.Close();
        }

        protected String getOrgCodeByUserName(String UserName)
        {
            String OrgCode = "";

            SqlConnection SqlConn = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConn.Open();

            SqlCommand SelectCommand = new SqlCommand("SELECT OrgCode FROM u_cls_Org WHERE Id = (SELECT OrgId FROM u_cls_UserInOrg WHERE UserName = @UserName)", SqlConn);

            SelectCommand.Parameters.Add("@UserName", SqlDbType.NVarChar, 4);
            SelectCommand.Parameters["@UserName"].Value = UserName;

            SqlDataReader OrgCodeReader = SelectCommand.ExecuteReader();

            while (OrgCodeReader.Read())
            {                
                OrgCode = OrgCodeReader["OrgCode"].ToString().Trim();
            }            

            OrgCodeReader.Close();
            SqlConn.Close();

            return OrgCode;
        }

        protected String getOrgNameByUserName(String UserName)
        {
            String OrgName = "";

            SqlConnection SqlConn = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConn.Open();

            SqlCommand SelectCommand = new SqlCommand("SELECT OrgName FROM u_cls_Org WHERE Id = (SELECT OrgId FROM u_cls_UserInOrg WHERE UserName=@UserName)", SqlConn);

            SelectCommand.Parameters.Add("@UserName", SqlDbType.NVarChar, 4);
            SelectCommand.Parameters["@UserName"].Value = UserName;

            SqlDataReader ResReader = SelectCommand.ExecuteReader();

            while (ResReader.Read())
            {
                //还是分开取得比较好，后面还有单独用到OrgCode的地方......
                //OrgCode = ResReader["OrgCode"].ToString() + "-" + ResReader["OrgName"].ToString();  
                OrgName = ResReader["OrgName"].ToString();
            }

            ResReader.Close();
            SelectCommand.Dispose();
            SqlConn.Close();

            return OrgName;
        }
    }
}